package org.ray.db;

import java.sql.Connection;
import java.sql.SQLException;

/**
 * Statis utility class for retrieving Connection and InitialContext objects.
 * 
 * @author Ray Wang
 * @since 1.0
 * @version 1.2
 */

public class DBUtils
{

//////////////////////////////////////////////////////////////////////////////
//// Class constants/variables
private static Connection  con = null;
private static final String url = "jdbc:sqlserver://";
private static final String devServerName= "sc-itappdev1";
private static final String portNumber = "1433";
private static final String databaseName= "IPDatabase";
private static final String userName = "u_librarian";
private static final String password = "p_librarian";


//FIXME? should we make this a lock ENV lookup not a global?

//////////////////////////////////////////////////////////////////////////////
//// Remaining (public or protected) static methods

/**
 * Retrieves database connection using JNDI.
 * 
 * @return Database Connection
 * @throws SQLException
 * 
 * @since 1.0
 */

private static String getConnectionUrl(){
		return url + devServerName + ":" + portNumber + ";databaseName=" + databaseName;
}

public static Connection getConnection ()
    throws SQLException
{
    try
    {
    	Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); 
        con = java.sql.DriverManager.getConnection(getConnectionUrl(),userName,password);
        if(con != null) 
        	System.out.println("Connection Successful!");
    }
    catch (Exception e)
    {
        e.printStackTrace();
        throw new SQLException("Unable to access JDBC, ", e.getMessage());
    }
    return con;
}

private static void closeConnection(){
    try{
         if(con != null)
              con.close();
         con = null;
    }catch(Exception e){
         e.printStackTrace();
    }
}

//public static Connection getConnectionToIPDB ()
//throws SQLException
//{
//	try
//	{
//	    Context jndi = new InitialContext();
//	    DataSource ds = (DataSource) jndi.lookup(IPDATASOURCE);
//	    return ds.getConnection();
//	}
//	catch (NamingException e)
//	{
//	    e.printStackTrace();
//	    throw new EJBException("Unable to access JDBC, ", e);
//	}
//}

public static void displayDbProperties(){
    java.sql.DatabaseMetaData dm = null;
    java.sql.ResultSet rs = null;
    try{
         con = getConnection();
         if(con != null){
              dm = con.getMetaData();
              System.out.println("Driver Information");
              System.out.println("\tDriver Name: "+ dm.getDriverName());
              System.out.println("\tDriver Version: "+ dm.getDriverVersion ());
              System.out.println("\nDatabase Information ");
              System.out.println("\tDatabase Name: "+ dm.getDatabaseProductName());
              System.out.println("\tDatabase Version: "+ dm.getDatabaseProductVersion());
              System.out.println("Avalilable Catalogs ");
              rs = dm.getCatalogs();
              while(rs.next()){
                   System.out.println("\tcatalog: "+ rs.getString(1));
              } 
              rs.close();
              rs = null;
              closeConnection();
         }else System.out.println("Error: No active Connection");
    }catch(Exception e){
         e.printStackTrace();
    }
    dm=null;
}     

}